package com.totoro.web;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.totoro.domain.Project;
import com.totoro.domain.PublicReply;
import com.totoro.domain.Tutor;

public class PublicReplyExcelView extends AbstractExcelView {

    private final int COLUMNS = 6;
    private final String SHEET_NAME = "PublicReply";
    private final short DEFAULT_COLUMN_WIDTH = 3500;
    private final short DEFAULT_ROW_HEIGHT = 490;
    private final String DEFAULT_FONT_NAME = "宋体";
    private final short DEFAULT_FONT_SIZE = 12;
    private final int REMARK_AREA_ROWS = 33;
    private final short TITLE_ROW_HEIGHT = 660;
    private final short TITLE_FONT_SIZE = 14;
    
    @Override
    protected void buildExcelDocument(Map<String, Object> model,
        HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        
        Project project = (Project)model.get("project");
        // 答辩小组意见
        PublicReply publicReply = project.getPublicReply();
        
        if (publicReply == null) {
            publicReply = new PublicReply();
            publicReply.setAchievementLevel("");
            publicReply.setCompleteness("");
            publicReply.setRemark("");
            publicReply.setReply(-1);
            publicReply.setStatement(-1);
        }
        
        int rowNumber = 0;
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName(DEFAULT_FONT_NAME);
        font.setFontHeightInPoints(TITLE_FONT_SIZE);
        
        HSSFSheet sheet = workbook.createSheet(SHEET_NAME);
        sheet.setHorizontallyCenter(true);
//        sheet.setDisplayGridlines(false);
        
        // 合并单元格区域
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, COLUMNS - 1));
        
        for (int i = 0; i < COLUMNS; i++) {
            sheet.setColumnWidth(i, DEFAULT_COLUMN_WIDTH);
        }
        
        HSSFRow titleRow = sheet.createRow(rowNumber);
        titleRow.setHeight(TITLE_ROW_HEIGHT);
        HSSFCell cell = titleRow.createCell(0);
        
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setFont(font);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        
        // 标题
        cell.setCellValue("山东建筑大学毕业设计（论文）答辩小组意见表");
        cell.setCellStyle(titleStyle);
        
        
        HSSFFont cellFont = workbook.createFont();
        cellFont.setFontName(DEFAULT_FONT_NAME);
        cellFont.setFontHeightInPoints(DEFAULT_FONT_SIZE);
        
        CellStyle cellStyle = workbook.createCellStyle();
        
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setFont(cellFont);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        
        // 题目样式
        CellStyle subjectCellStyle = workbook.createCellStyle();
        subjectCellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        subjectCellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        subjectCellStyle.setFont(cellFont);
        
        
        CellStyle underLineCellStyle = workbook.createCellStyle();
        underLineCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        underLineCellStyle.setFont(cellFont);
        underLineCellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        underLineCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        
        // 题目
        rowNumber++;
        HSSFRow subjectRow = sheet.createRow(rowNumber);
        HSSFCell subjectCell = subjectRow.createCell(0);
        
        int cellWidth = 0;

        cellWidth = COLUMNS / 6;
        
        for (int i = cellWidth; i < COLUMNS - 1; i++) {
            subjectRow.createCell(i).setCellStyle(underLineCellStyle);
        }
        
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, cellWidth - 1));
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, cellWidth, COLUMNS - 2));
        
        subjectRow.setHeight(DEFAULT_ROW_HEIGHT);
        subjectCell.setCellValue("题目：");
        subjectCell.setCellStyle(subjectCellStyle);
        
        subjectRow.getCell(1).setCellValue(project.getTitle());
        
        // 提示内容样式
        CellStyle promptCellStyle = workbook.createCellStyle();
        promptCellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        promptCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        promptCellStyle.setFont(cellFont);
        
        // 班级 
        rowNumber++;
        HSSFRow classRow = sheet.createRow(rowNumber);
        classRow.setHeight(DEFAULT_ROW_HEIGHT);
        HSSFCell classCell = classRow.createCell(0);
        classCell.setCellValue("班级：");
        classCell.setCellStyle(promptCellStyle);
        
        HSSFCell classValueCell = classRow.createCell(cellWidth);
        classValueCell.setCellStyle(cellStyle);
        classValueCell.setCellValue(project.getStudent().getStudentClass().getDescription());
        
        // 姓名
        HSSFCell nameCell = classRow.createCell(cellWidth + 1);
        nameCell.setCellValue("姓名：");
        nameCell.setCellStyle(promptCellStyle);
        
        HSSFCell nameValueCell = classRow.createCell(cellWidth * 2 + 1);
        nameValueCell.setCellValue(project.getStudent().getName());
        nameValueCell.setCellStyle(cellStyle);

        // 学号
        HSSFCell noCell = classRow.createCell(3 * cellWidth + 1);
        noCell.setCellValue("学号：");
        noCell.setCellStyle(promptCellStyle);
        
        HSSFCell noValueCelll = classRow.createCell(cellWidth * 4 + 1);
        noValueCelll.setCellValue(project.getStudent().getNo());
        noValueCelll.setCellStyle(cellStyle);
        
        rowNumber++;
        sheet.createRow(rowNumber);
        
        Map<String, Object> map = new TreeMap<String, Object>();
        map.put("1-完成任务书规定的要求与水平评分（0-10分）", "-1".equals(publicReply.getCompleteness()) ? "" : publicReply.getCompleteness());
        map.put("2-论文与实物的质量评分（0-10分）", "-1".equals(publicReply.getAchievementLevel()) ? "" : publicReply.getAchievementLevel());
        map.put("3-论文内容的答辩陈述评分（0-10分）", publicReply.getStatement() == -1 ? "" : publicReply.getStatement());
        map.put("4-回答问题的正确性评分（0-10分）", publicReply.getReply() == -1 ? "" : publicReply.getReply());
        
        rowNumber = editMarkContentArea(workbook, rowNumber, map);
        
        Collection<Tutor> tutors = project.getPublicReplyGroup().getTutor();
        StringBuilder builder = new StringBuilder();
        Iterator<Tutor> iterator = tutors.iterator();
        Tutor tutor = null;

        while(iterator.hasNext()) {
            tutor = iterator.next();
            builder.append(tutor.getName());
            builder.append(", ");
        }
        
        builder.delete(builder.length() - 2, builder.length());
        
        rowNumber = editRemarkArea(workbook, rowNumber, publicReply.getRemark(), builder.toString());
        
        String excelName = project.getStudent().getNo() + project.getStudent().getName() + "-答辩小组意见表.xls";

        response.setHeader("Content-Disposition", "attachment;filename=" 
            + new String(excelName.getBytes(), "ISO-8859-1"));
    }

    // 评分区域编辑
    private int editMarkContentArea(HSSFWorkbook workbook, int rowNumber, Map<String, Object> map) {
        
        HSSFFont font = workbook.createFont();
        font.setFontName(DEFAULT_FONT_NAME);
        font.setFontHeightInPoints(DEFAULT_FONT_SIZE);
        
        CellStyle leftCellStyle = workbook.createCellStyle();
        leftCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        leftCellStyle.setFont(font);
        leftCellStyle.setWrapText(true);
        
        // 设置边框
        leftCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        leftCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        leftCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        
        CellStyle rightCellStyle = workbook.createCellStyle();
        rightCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        rightCellStyle.setFont(font);
        
        // 设置边框
        rightCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        rightCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        rightCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        
        CellStyle middleCellStyle = workbook.createCellStyle();
        
        middleCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        middleCellStyle.setFont(font);
        
        // 设置边框
        middleCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        middleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        
        CellStyle borderCellStyle = workbook.createCellStyle();
        borderCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        borderCellStyle.setFont(font);
        
        // 设置边框
        borderCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        borderCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        borderCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        borderCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        
        CellStyle valueCellStyle = workbook.createCellStyle();
        
        valueCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        valueCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        valueCellStyle.setFont(font);
        
        // 设置边框
        valueCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        valueCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        valueCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        valueCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        
        HSSFCell[] cells = new HSSFCell[COLUMNS];
        
        int keyCellWidth = 0;
        int valueCellWidth = 0;
        
        valueCellWidth = COLUMNS / (2 * 3);
        keyCellWidth = COLUMNS / 3;
        
        List<String> keyList = new ArrayList<String>(map.keySet());
        Collections.sort(keyList);
        
        Iterator<String> iter = keyList.iterator();
        
        while (iter.hasNext()) {
            
            HSSFSheet sheet = workbook.getSheet(SHEET_NAME);
            HSSFRow row = sheet.createRow(rowNumber);
            row.setHeight((short)(2 * DEFAULT_ROW_HEIGHT));
            
            sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, keyCellWidth - 1));
            sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, keyCellWidth, keyCellWidth + valueCellWidth - 1));
            sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, valueCellWidth + keyCellWidth, COLUMNS - valueCellWidth -1));
            sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, COLUMNS - valueCellWidth, COLUMNS - 1));
            
            String key = iter.next();
            
            for (int i = 0; i < COLUMNS; i++) {
                cells[i] = row.createCell(i);
                
                if (i == 0 || i == keyCellWidth + valueCellWidth) {
                    if (keyCellWidth == 1) {
                        cells[i].setCellStyle(borderCellStyle);
                    } else {
                        cells[i].setCellStyle(leftCellStyle);
                    }
                    cells[i].setCellValue(key.replaceAll("^\\d-", ""));
                } else if (i == keyCellWidth - 1 || i == COLUMNS - valueCellWidth - 1) {
                    if (keyCellWidth == 1) {
                        cells[i].setCellStyle(borderCellStyle);
                    } else {
                        cells[i].setCellStyle(rightCellStyle);
                    }
                } else {
                    cells[i].setCellStyle(middleCellStyle);
                }
                
                // 分数单元格设置
                if (i == keyCellWidth || i == COLUMNS - valueCellWidth) {
                    if (valueCellWidth == 1) {
                        cells[i].setCellStyle(valueCellStyle);
                    }
                    cells[i].setCellValue(map.get(key).toString());
                    
                    if (i < COLUMNS - valueCellWidth && iter.hasNext()) {
                        key = iter.next();
                    }
                }
            }
            
            rowNumber++;
        }
        
        return rowNumber;
    }

    /**
     * 意见填写区域编辑
     * 
     * @param workbook 编辑工作簿
     * @param rowNumber 当前行数
     * @param remark 答辩小组意见
     * @param turtorNames 答辩小组成员名
     * @return 编辑行下一行的行数
     */
    private int editRemarkArea(HSSFWorkbook workbook, int rowNumber, String remark, String turtorNames) {
        
        HSSFFont font = workbook.createFont();
        font.setFontName(DEFAULT_FONT_NAME);
        font.setFontHeightInPoints(DEFAULT_FONT_SIZE);
        
        CellStyle remarkAreaValueCellStyle = workbook.createCellStyle();
        remarkAreaValueCellStyle.setFont(font);
        remarkAreaValueCellStyle.setWrapText(true);
        remarkAreaValueCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        
        CellStyle promptCellStyle = workbook.createCellStyle();
        promptCellStyle.setFont(font);
        promptCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        promptCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        
        CellStyle remarkAreaLeftCellStyle = workbook.createCellStyle();
        remarkAreaLeftCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaLeftCellStyle.setFont(font);
        remarkAreaLeftCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        remarkAreaLeftCellStyle.setWrapText(true);
        
        CellStyle remarkAreaBottomCellStyle = workbook.createCellStyle();
        remarkAreaLeftCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaBottomCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        remarkAreaBottomCellStyle.setFont(font);
        
        CellStyle remarkAreaLeftBottomCellStyle = workbook.createCellStyle();
        remarkAreaLeftBottomCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaLeftBottomCellStyle.setFont(font);
        remarkAreaLeftBottomCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        remarkAreaLeftBottomCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        
        CellStyle remarkAreaRightCellStyle = workbook.createCellStyle();
        remarkAreaRightCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaRightCellStyle.setFont(font);
        remarkAreaRightCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        
        CellStyle remarkAreaRightBottomCellStyle = workbook.createCellStyle();
        remarkAreaRightBottomCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaRightBottomCellStyle.setFont(font);
        remarkAreaRightBottomCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        remarkAreaRightBottomCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        
        HSSFSheet sheet = workbook.getSheet(SHEET_NAME);
        HSSFRow remarkRow = sheet.createRow(rowNumber);
        HSSFCell remarkCell = remarkRow.createCell(0);
        
        remarkCell.setCellValue("答辩小组意见:");
        remarkCell.setCellStyle(promptCellStyle);
        
        remarkRow.createCell(COLUMNS - 1).setCellStyle(remarkAreaRightCellStyle);
        remarkRow.setHeight(DEFAULT_ROW_HEIGHT);
        
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, COLUMNS - 1));
        
        HSSFRow tmpRow = null;
        for (int i = 1; i < REMARK_AREA_ROWS; i++) {
            tmpRow = sheet.createRow(rowNumber + i);
            tmpRow.createCell(0).setCellStyle(remarkAreaLeftCellStyle);
            tmpRow.createCell(COLUMNS - 1).setCellStyle(remarkAreaRightCellStyle);
            
            if (i == 1) {
                tmpRow.getCell(0).setCellValue("    " + remark);
            }
        }
        
        sheet.addMergedRegion(new CellRangeAddress(rowNumber + 1, rowNumber + REMARK_AREA_ROWS - 5, 0, COLUMNS - 1));
        
        int signatureIndex = COLUMNS / 3 * 2 - 1;
        HSSFCell signatureCell = sheet.getRow(rowNumber + REMARK_AREA_ROWS - 4).createCell(signatureIndex);
        signatureCell.setCellStyle(remarkAreaValueCellStyle);
        signatureCell.setCellValue("答辩小组成员签字：");
        
        sheet.addMergedRegion(new CellRangeAddress(
            rowNumber + REMARK_AREA_ROWS - 4, rowNumber + REMARK_AREA_ROWS - 4, 
            signatureIndex, COLUMNS - 1));
        
        HSSFCell valueCell = sheet.getRow(rowNumber + REMARK_AREA_ROWS - 3).createCell(signatureIndex);
        valueCell.setCellStyle(remarkAreaValueCellStyle);
        
        valueCell.setCellValue(turtorNames);
        
        sheet.addMergedRegion(new CellRangeAddress(
            rowNumber + REMARK_AREA_ROWS - 3, rowNumber + REMARK_AREA_ROWS - 2, 
            signatureIndex, COLUMNS - 1));
        
        HSSFCell dateCell = sheet.getRow(rowNumber + REMARK_AREA_ROWS - 1).createCell(COLUMNS - 2);
        dateCell.setCellStyle(remarkAreaValueCellStyle);
        
        dateCell.setCellValue(new SimpleDateFormat("yyyy 年 MM 月 dd 日").format(new Date()));
        
        sheet.addMergedRegion(new CellRangeAddress(rowNumber + REMARK_AREA_ROWS - 1,
            rowNumber + REMARK_AREA_ROWS - 1, COLUMNS - 2, COLUMNS - 1));
        
        rowNumber += REMARK_AREA_ROWS;
        HSSFCell tmpCell = null;
        tmpRow = sheet.createRow(rowNumber);
        for (int i = 0; i < COLUMNS; i++) {
            tmpCell = tmpRow.createCell(i);
            
            if (i == 0) {
                tmpCell.setCellStyle(remarkAreaLeftBottomCellStyle);
            } else if (i == COLUMNS - 1) {
                tmpCell.setCellStyle(remarkAreaRightBottomCellStyle);
            } else {
                tmpCell.setCellStyle(remarkAreaBottomCellStyle);
            }
        }
        
        return rowNumber;
    }
    
}
